const dbPool = require('../sql/sql_pool');

class Controller {
    static async getAll(username) {
        let sql = `SELECT 
            data.id, type, date, resume, description, amount
            FROM data, users
            WHERE
            data.user_id = users.id AND users.username = '${username}'`;

        // 执行查询, 循环结果集, 暂存每一行的数据
        const data = [];
        try {
            const [result] = await dbPool.query(sql);
            result.forEach(row => {
                data.push({
                    id: row.id,
                    type: row.type,
                    date: row.date,
                    resume: row.resume,
                    description: row.description,
                    amount: Number(row.amount) / 100
                })
            })
            return data;
        } catch (err) {
            console.log(err)
            return []
        }
    }

    // 添加账单
    // args: username, type, date, amount, resume, description
    static async addBill(...args) {

        let sql = "INSERT INTO `data`(`user_id`,`type`,`date`,`amount`,`resume`,`description`) VALUES((SELECT id FROM users WHERE username =?),?,?,?,?,?);";
        await dbPool.query(sql, args);

        // 获取最后插入数据的 ID
        let idSql = `SELECT LAST_INSERT_ID() as id`;
        const [idResult] = await dbPool.query(idSql);

        // 返回新插入账单的 ID
        return idResult[0].id;
    }

    // 删除账单
    static async deleteBills(ids) {
        // 构建SQL语句，使用IN关键字来删除多个id对应的记录
        let sql = `DELETE FROM data WHERE id IN (?)`;

        // 执行删除操作，将ids数组作为参数传递
        await dbPool.query(sql, [ids]);
    }

    // 更新账单
    static async updateBill(id, type, date, amount, resume, description) {
        let sql = `UPDATE data SET type =?, date =?, amount =?, resume =?, description =? WHERE id = ?`;
        await dbPool.query(sql, [type, date, amount, resume, description, id]);
    }
}

module.exports = Controller;
